package com.dai.chun.util;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.struts2.ServletActionContext;

import com.dai.chun.dao.model.Wage;
import com.dai.chun.dbc.DBManager;

public class DbExcel {
	
	
	public void createExcel(ResultSet rs) {
		//得到列数 行数
		/*int columnCount = 0;
		int rowCount = 0;
		ResultSetMetaData rsmd;
		try {
			rs.last();
			rowCount = rs.getRow(); //获得ResultSet的总行数
			rs.first();
			rsmd = rs.getMetaData();
			columnCount = rsmd.getColumnCount();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}*/
		try {
	          // 打开文件
			String url = ServletActionContext.getServletContext().getRealPath("/");
	          WritableWorkbook book = Workbook.createWorkbook(new File(url+"test.xls"));
	          // 生成名为“第一页”的工作表，参数0表示这是第一页
	          WritableSheet sheet = book.createSheet("本月工资", 0);
	         //写表头
	          String[] th={"员工编号","员工姓名","部门","月份","津贴工资","处罚","奖金","基本工资","税费","三险一金","应发工资","实发工资"};
	          for(int i=0;i<th.length;i++){
	        	  // 在Label对象的构造子中指名单元格位置
	        	  Label label = new Label(i, 0, th[i]);//列 行 内容
	        	  sheet.addCell(label);
	          }
	         //填内容  行数 列数都不需要了 行数直接在表头指定了 列数rs.next()就行了
	         /* for(int j=0;j<rowCount;j++){
	         
	          }*/
	          int j=1;
	          while(rs.next()){
	        	  sheet.addCell(new Label(0,j, rs.getString("wage_emp_num"))) ;
	        	  sheet.addCell(new Label(1, j, rs.getString("wage_emp_name"))) ;
	        	  sheet.addCell(new Label(2, j,rs.getString("wage_emp_dept"))) ;
	        	  sheet.addCell(new Label(3, j,  rs.getString("wage_month"))) ;
	        	  sheet.addCell(new Label(4, j,  rs.getString("wage_subsidy_sum"))) ;
	        	  sheet.addCell(new Label(5, j,  rs.getString("wage_penalty"))) ;
	        	  sheet.addCell(new Label(6, j, rs.getString("wage_reward"))) ;
	        	  sheet.addCell(new Label(7, j, rs.getString("wage_base"))) ;
	        	  sheet.addCell(new Label(8, j, rs.getString("wage_tax"))) ;
	        	  sheet.addCell(new Label(9, j, rs.getString("wage_sxyj"))) ;
	        	  sheet.addCell(new Label(10, j, rs.getString("wage_should"))) ;
	        	  sheet.addCell(new Label(11, j, rs.getString("wage_real"))) ;
	        	  j++;
	        	  System.out.println( rs.getString("wage_real"));
	          }
	          // 写入数据并关闭文件
	          book.write();
	          book.close();

	      } catch (Exception e) {
	          System.out.println(e);
	      }
	}
	
	public void getData(Wage w) {
		DBManager manager = DBManager.getInstance();
		Statement statement = null ;
		Connection conn = null;
		ResultSet res=null;
		String sql="select wage_emp_num,wage_emp_name,wage_emp_dept,wage_month,wage_subsidy_sum,wage_penalty,wage_reward,wage_base,wage_tax,wage_sxyj,wage_should,wage_real from t_wage where wage_month=date_format(now(),'%Y-%m')";
		try {
			conn = manager.getConnection();
			statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            res = statement.executeQuery(sql);
            createExcel(res);
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, statement, res);
		}
	}
	
	public static void main(String[] args) {
		DbExcel de=new DbExcel();
		Wage w=new Wage();
		w.setWage_month(new SimpleDateFormat("yyyy-MM").format(new Date()));
		de.getData(w);
	}
}
